package com.enfry.common.util.excel;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;

import jxl.Cell;
import jxl.Sheet;

public class SheetParser {
	private SheetStruct struct;
	//表单名
    private String sheetName;
    // excel标题所在行数，用于读取excel记录，从标题后开始读取,默认为第一行开始读取
    private int titleRowSpan = 1;
    private HashMap<Integer, TD> tdMap = new HashMap<Integer, TD>();
    private HashMap<Integer, String> tdKeyMap = new HashMap<Integer, String>();
	public String getSheetName() {
		return sheetName;
	}
	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}
	public int getTitleRowSpan() {
		return titleRowSpan;
	}
	public void setTitleRowSpan(int titleRowSpan) {
		this.titleRowSpan = titleRowSpan;
	}
	public HashMap<Integer, TD> getTdMap() {
		return tdMap;
	}
	public void setTdMap(HashMap<Integer, TD> tdMap) {
		this.tdMap = tdMap;
	}
	public HashMap<Integer, String> getTdKeyMap() {
		return tdKeyMap;
	}
	public void setTdKeyMap(HashMap<Integer, String> tdKeyMap) {
		this.tdKeyMap = tdKeyMap;
	}
	
	public SheetStruct getStruct() {
		return struct;
	}
	public void setStruct(SheetStruct struct) {
		this.struct = struct;
	}
	
	private void parseLabel(Map<String, Object> data, int rowIdx, int tdIdx, Cell cell) {
        String cellValue = "";
        if (cell != null) {
            cellValue = ExcelFileUtil.getCellContent(cell);
            parsrLabelValue(data, rowIdx, tdIdx, cellValue);
        }else{
        	parsrLabelValue(data, rowIdx, tdIdx, cellValue);
        }
    }
	
	private void parseLabel(Map<String, Object> data, int rowIdx, int tdIdx, org.apache.poi.ss.usermodel.Cell cell) {
        String cellValue = "";
        if (cell != null) {
            cellValue = ExcelFileUtil.getCellContent(cell);
            parsrLabelValue(data, rowIdx, tdIdx, cellValue);
        }else{
        	parsrLabelValue(data, rowIdx, tdIdx, cellValue);
        }
    }
	
	private void parsrLabelValue(Map<String, Object> data, int rowIdxOfExcel, int tdIdx, String cellValue) {
        TD td = tdMap.get(tdIdx);
        String tdKey = tdKeyMap.get(tdIdx);
        String value = StringUtils.trimToEmpty(cellValue);
        data.put(tdKey, replaceBlank(value));
    }
	
	private String replaceBlank(String str) {
	    String dest = "";
	    if (str!=null) {
	        Pattern p = Pattern.compile("\\n");
	        Matcher m = p.matcher(str);
	        dest = m.replaceAll("");
	    }
	    return dest;
	}
	
	public List<Map<String, Object>> parseSheet(Map context, Sheet sheet) throws Exception {
        int x, y;
        int colMax;
        SheetStruct struct = getStruct();
        colMax = struct.getBody().getTR(0).getTds().size();
        for (int i = 0; i < colMax; i++) {
            tdMap.put(i, struct.getBody().getTR(0).getTD(i));
            String key = struct.getBody().getTR(0).getTD(i).getLabel();
            key = key.substring(1, key.length() - 1);
            tdKeyMap.put(i, key);
        }

        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        int emptyRowCount = 0;
        for (int rowIdx = this.titleRowSpan; rowIdx < sheet.getRows() ; rowIdx++) {
            if (ExcelFileUtil.isEmptyRow(sheet.getRow((rowIdx)))) {
                emptyRowCount++;
                continue;
            }

            Map<String, Object> map = new HashMap<String, Object>();
            //配置文件中定义的总列数。
            for (int colIdx = 0; colIdx < colMax; colIdx++) {
                Cell cell = sheet.getCell(colIdx, rowIdx);
                parseLabel(map, rowIdx, colIdx, cell);
            }
            dataList.add(map);
        }

        if((sheet.getRows()-emptyRowCount) > struct.getMaxRowCount()){
            String msg = "Excel文件的" + getSheetName() + "表单记录数超过配置文件中允许的最大值" + struct.getMaxRowCount();
                throw new Exception(msg);
        }
        return dataList;
    }
	
	public List<Map<String, Object>> parseSheet(Map context, org.apache.poi.ss.usermodel.Sheet sheet) throws Exception {
        int x, y;
        int colMax;
        SheetStruct struct = getStruct();
        colMax = struct.getBody().getTR(0).getTds().size();
        for (int i = 0; i < colMax; i++) {
            tdMap.put(i, struct.getBody().getTR(0).getTD(i));
            String key = struct.getBody().getTR(0).getTD(i).getLabel();
            key = key.substring(1, key.length() - 1);
            tdKeyMap.put(i, key);
        }

        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        int emptyRowCount = 0;
        int countRowNum = 0;
        for (Row row : sheet) {
        	 countRowNum++;

        	 if (ExcelFileUtil.isEmptyRow(row)) {
        		 emptyRowCount++;
        		 continue;
        	 }

             if(row.getRowNum()<this.titleRowSpan){
                continue;
             }

        	 Map<String, Object> map = new HashMap<String, Object>();
        	 for (org.apache.poi.ss.usermodel.Cell cell : row) {
        		 parseLabel(map, row.getRowNum(), cell.getColumnIndex(), cell);
        	 }	
        	 dataList.add(map);
        }
        
        if((countRowNum-emptyRowCount) > struct.getMaxRowCount()){
        	String msg = "Excel文件的" + getSheetName() + "表单记录数超过配置文件中允许的最大值" + struct.getMaxRowCount();
            throw new Exception(msg);
        }
        	
//        for (int rowIdx = this.titleRowSpan; rowIdx < sheet.getLastRowNum()+1 ; rowIdx++) {
//            if (ExcelFileUtil.isEmptyRow(sheet.getRow((rowIdx)))) {
//                emptyRowCount++;
//                continue;
//            }
//
//            Map<String, Object> map = new HashMap<String, Object>();
//            //配置文件中定义的总列数。
//            for (int colIdx = 0; colIdx < colMax; colIdx++) {
//            	org.apache.poi.ss.usermodel.Cell cell = sheet.getRow(rowIdx).getCell(colIdx);
//                parseLabel(map, rowIdx, colIdx, cell);
//            }
//            dataList.add(map);
//        }
//
//        if((sheet.getLastRowNum()-emptyRowCount) > struct.getMaxRowCount()){
//            String msg = "Excel文件的" + getSheetName() + "表单记录数超过配置文件中允许的最大值" + struct.getMaxRowCount();
//                throw new Exception(msg);
//        }
        return dataList;
    }
    
    
}
